import pymysql
import xlwt
import datetime
# 数据库操作
config = {
    "host": "1.117.167.225",
    "user": "root",
    "password": "Aa1234zxcv",
    "database": "qc_live",
    "charset": "utf8"
}


# 查询表里所有数据
def find_all_only(uid):
    conn = pymysql.connect(**config)
    time = '2023-02-08'
    time1 = '2023-02-09'
    cursor = conn.cursor()
    tab_list = []
    # uid = 105367
    for day in range(0, 12):
        time = (datetime.datetime.now() + datetime.timedelta(days=-day)).strftime('%Y-%m-%d')
        time1 = (datetime.datetime.now() + datetime.timedelta(days=-day+1)).strftime('%Y-%m-%d')

        query = ('select * from t_live_statistics where uid ='+str(uid)+' and time >= "'+str(time)+'" and time < "'+str(time1)+'" and live_income>0 ORDER BY live_income DESC')
        cursor.execute(query)
        result = cursor.fetchall()
        for (item) in result:
            # print(item)
            my_map = {'uid': item[1], 'income': item[2], 'showincome': item[4], 'video': item[6]}
            my_map['time'] = time

            # 查询当前直播间所有上榜的用户 上榜人数金额
            # select * from t_live_user_statistics where room_id = 102871 and time >= '2023-02-02'
            query1 = ('select * from t_live_user_statistics where room_id = '+str(item[1])+' and time >= "'+ str(time)+'" and time < "'+str(time1)+'"')
            cursor.execute(query1)
            result1 = cursor.fetchall()
            gx_list = ''
            for (item1) in result1:
                # print('贡献的用户：'+item1[2] + ' 贡献值：'+item1[3])
                gx_list += str(item1[2]) + ' 贡献值：' + str(item1[3]) + '\n '
            my_map['gx_list'] = gx_list

            # 直播间的贡献值分类 直送 背包 type =1 直送  2 背包
            # select * from t_gift_user_statistics where room_id = 104760 and time >= '2023-02-02' order by coin desc
            query2 = ('select * from t_gift_user_statistics where room_id = '+str(item[1])+' and time >= "'+str(time)+'" and time < "'+str(time1)+'" order by coin desc')
            cursor.execute(query2)
            result2 = cursor.fetchall()
            gift_coin = 0
            bg_coin = 0
            for (item2) in result2:
                if item2[4] == 1:
                    gift_coin += item2[3]
                else:
                    bg_coin += item2[3]
            my_map['gift'] = gift_coin
            my_map['bggift'] = bg_coin

            # 查询直播间当日的直播任务领取金额
            # select SUM(cost) from t_live_task_record where uid = 102871 and time >= '2023-02-01' and time<'2023-02-02'
            query3 = ('select SUM(cost) from t_live_task_record where uid = ' + str(item[1]) + ' and time >= "' + str(
                time) + '" and time < "' + str(time1) + '"')
            cursor.execute(query3)
            result3 = cursor.fetchall()
            for (item3) in result3:
                my_map['live_task'] = item3[0]

            query4 = 'select * from t_userinfo where uid =' + str(item[1])
            cursor.execute(query4)
            result4 = cursor.fetchall()
            for (item4) in result4:
                my_map['nick'] = item4[2]
            # 添加数据到list里面
            tab_list.append(my_map)



    # 生成报表
    work_book = xlwt.Workbook(encoding='utf-8')
    sheet = work_book.add_sheet('主播贡献榜')
    sheet.write(0, 0, '用户ID')
    sheet.write(0, 1, '昵称')
    sheet.write(0, 2, '上榜')
    sheet.write(0, 3, '显示的上榜')
    sheet.write(0, 4, '直送礼物金额')
    sheet.write(0, 5, '背包上榜金额')
    sheet.write(0, 6, '直送占比')
    sheet.write(0, 7, '背包占比')
    sheet.write(0, 8, '视频直播小时')
    sheet.write(0, 9, '直播任务领取金额')
    sheet.write(0, 10, '直播贡献列表')
    sheet.write(0, 11, '日期')


    for index in range(len(tab_list)):
        sheet.write(index + 1, 0, tab_list[index]['uid'])
        sheet.write(index + 1, 1, tab_list[index]['nick'])
        sheet.write(index + 1, 2, tab_list[index]['income'])
        sheet.write(index + 1, 3, tab_list[index]['showincome'])
        sheet.write(index + 1, 4, tab_list[index]['gift'])
        sheet.write(index + 1, 5, tab_list[index]['bggift'])
        sheet.write(index + 1, 6, '0%' if tab_list[index]['gift'] == 0 else str(
            round(tab_list[index]['gift'] / tab_list[index]['income'], 4) * 100) + '%')
        sheet.write(index + 1, 7, '0%' if tab_list[index]['bggift'] == 0 else str(
            round(tab_list[index]['bggift'] / tab_list[index]['income'], 4) * 100) + '%')
        sheet.write(index + 1, 8, tab_list[index]['video'] / 60)
        sheet.write(index + 1, 9, tab_list[index]['live_task'])
        sheet.write(index + 1, 10, tab_list[index]['gx_list'])
        sheet.write(index + 1, 11, tab_list[index]['time'])


    work_book.save('C:\\Users\\Administrator\\Documents\\xiaolu\\'+str(uid)+'_单一主播榜单.xls')
    # return tab_list
    cursor.close()
    conn.close()


if __name__ == '__main__':
    # 主播上榜整体情况
    find_all_only(101372)
